package com.example.swaggerDemo.service;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.example.swaggerDemo.Dao.SysUserDao;
import com.example.swaggerDemo.entity.SysUserEntity;

import com.example.swaggerDemo.entity.usercount;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestParam;


import javax.annotation.Resource;

@Resource
@Service
public class UserService implements SysUserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     *查询当前用户下属用户
     * @param upopenid 用户wxid
     * @param level 用户等级
     * @return
     * @throws SQLException
     */
    @Override
    public List<SysUserEntity> findByUpopenid(@RequestParam("upopenid") String upopenid, @RequestParam("level") String level,@RequestParam("username") String username) throws SQLException {
        String sql = "";

        if (username!=null&&username!="")
        {
            sql = "select * from fa_user WHERE username LIKE " +"'%"+username+"%'"  + "AND upopenid= '" +  upopenid + "'"+" AND level="+"'" +  level + "'";

        }else {
            sql = "select * from fa_user WHERE  upopenid= '" +  upopenid + "'"+" AND level="+"'" +  level + "'";
        }
        sql=sql+" order by id DESC";
        List<SysUserEntity> dto= jdbcTemplate.query(sql, new BeanPropertyRowMapper(SysUserEntity.class));
        return dto;
    }



    /**
     *查询当前用户下属用户
     * @param upopenid 用户wxid
     * @param level 用户等级
     * @return
     * @throws SQLException
     */
    @Override
    public List<SysUserEntity> findNoByUpopenid(@RequestParam("upopenid") String upopenid, @RequestParam("level") String level,@RequestParam("username") String username) throws SQLException {
        String sql = "";

        if (username!=null&&username!="") {
            sql = "select * from fa_user WHERE  upopenid= '" +  upopenid + "'"+" AND level !="+"'" +  level + "'"+" AND username LIKE" +"'%"+username+"%'"+" AND level !="+"'"+1+"'";
        }
        else {
            sql = "select * from fa_user WHERE  upopenid= '" +  upopenid + "'"+" AND level !="+"'" +  level + "'"+" AND level !="+"'"+1+"'";
        }
        sql=sql+" order by id DESC";
        List<SysUserEntity> dto= jdbcTemplate.query(sql, new BeanPropertyRowMapper(SysUserEntity.class));
        return dto;
    }




    /**
     * 管理按用户级别查询
     * @param level
     * @return
     * @throws SQLException
     */
    @Override
    public List<SysUserEntity> adminfinduser( @RequestParam("level") String level,@RequestParam("username") String username, @RequestParam("pageindex") int pagindex, @RequestParam("pagesize") int pagesize) throws SQLException {
        String sql = "";

        if ((level!=""&&level!=null)&&(username!=null&&username!=""))
        {
            sql = "select * from fa_user WHERE username LIKE " +"'%"+username+"%'"  + "AND level =" + "'" + level + "'"+
                    " order by id DESC " + "LIMIT " + pagindex + "," + pagesize;
        }
        else {
            sql = "select * from fa_user WHERE   level =" + "'" + level + "'"
                    + " order by id DESC " + "LIMIT " + pagindex + "," + pagesize;
        }
        List<SysUserEntity> dto= jdbcTemplate.query(sql, new BeanPropertyRowMapper(SysUserEntity.class));
        return dto;
    }


    /**
     *查询当前用户信息
     * @param id 用户id
     * @return
     * @throws SQLException
     */
    @Override
    public  SysUserEntity findByid(@RequestParam("id") String id) throws SQLException {
        String sql = "select * from fa_user WHERE  id= '" +  id + "'";
        SysUserEntity dto=null;
        try { dto = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<SysUserEntity>(SysUserEntity.class));
        }
        catch(Exception ex){
            dto=null;
        }
        return dto;
    }

    /**
     *查询所有
     * @return
     * @throws SQLException
     */
    @Override
    public   List<SysUserEntity> findall() throws SQLException {
        String sql = "select * from fa_user ";
        List<SysUserEntity> dto= jdbcTemplate.query(sql, new BeanPropertyRowMapper(SysUserEntity.class));
        return dto;
    }

    /**
     *查询当前用户信息
     * @param openid openid
     * @return
     * @throws SQLException
     */
    @Override
    public  SysUserEntity findByOpenid(@RequestParam("openid") String openid) throws SQLException {
        String sql = "select * from fa_user WHERE  openid= '" +  openid + "'";
        SysUserEntity dto=null;
        try{
            dto = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<SysUserEntity>(SysUserEntity.class));
        }
        catch(Exception ex){

        }

        return dto;
    }

    /**
     * 注册用户
     * @param username
     * @param nickname
     * @param mobile
     * @param avatar
     * @param level
     * @param prevtime
     * @param logintime
     * @param jointime
     * @param createtime
     * @param updatetime
     * @param status
     * @param openid
     * @param materielA
     * @param materielB
     * @param address
     * @param upopenid
     * @return
     */
    @Override
    public int insert(@RequestParam("username") String username, @RequestParam("nickname") String nickname
            , @RequestParam("mobile") String mobile, @RequestParam("avatar") String avatar
            , @RequestParam("level") String level, @RequestParam("prevtime") Integer prevtime
            , @RequestParam("logintime") Integer logintime, @RequestParam("jointime") Integer jointime
            , @RequestParam("createtime") Integer createtime, @RequestParam("updatetime") Integer updatetime
            , @RequestParam("status") String status, @RequestParam("openid") String openid
            , @RequestParam("materielA") String materielA, @RequestParam("materielB") String materielB
            , @RequestParam("address") String address,@RequestParam("upopenid")String upopenid) {

        String sql = "insert into fa_user(username,nickname,mobile,avatar,level,prevtime,logintime,jointime,createtime,updatetime,status,openid,materielA,materielB,address,upopenid,isadmin) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql,username,nickname,mobile,avatar,level,prevtime,logintime,jointime,createtime,updatetime,status,openid,materielA,materielB,address,upopenid,0);
    }


    /**
     * 修改用户基础信息。
     * @param username
     * @param mobile
     * @param address
     * @param id
     * @return
     */
    @Override
    public int update( @RequestParam("username") String username, @RequestParam("mobile") String mobile, @RequestParam("address") String address, @RequestParam("id") String id) {
        String sql = "update  fa_user set username=?,mobile=?,address=? where id=?";
        return jdbcTemplate.update(sql,username,mobile,address,id);
    }

    /**
     * 修改用户身份证
     * @param sfz
     * @param id
     * @return
     */
    @Override
    public int updatesfz( @RequestParam("sfz") String sfz, @RequestParam("id") String id) {
        String sql = "update  fa_user set bio=? where id=?";
        return jdbcTemplate.update(sql,sfz,id);
    }



    //更新最近访问时间和次数
    @Override
    public int updatehonmetime(@RequestParam("maxsuccessions") int maxsuccessions, @RequestParam("id") String id) {
        Integer updatetime= Math.toIntExact(System.currentTimeMillis() / 1000);
        String sql = "update  fa_user set jointime=?,maxsuccessions=? where id=?";
        return jdbcTemplate.update(sql,updatetime,maxsuccessions,id);
    }


    /**
     * 修改用户物料信息
     * @param materielA
     * @param materielB
     * @param id
     * @return
     */
    @Override
    public int updateMateriel(@RequestParam("materielA") Integer materielA, @RequestParam("materielB") Integer materielB,
                              @RequestParam("id") String id) {
        String sql = "update  fa_user set materielA=?,materielB=? where id=?";
        return jdbcTemplate.update(sql,materielA,materielB,id);
    }

    /**
     * 修改用户上级id
     * @param upopenid
     * @param id
     * @return
     */
    @Override
    public int updateUpopenid(@RequestParam("upopenid")String upopenid,
                              @RequestParam("id") Integer id) {
        String sql = "update  fa_user set upopenid=? where id=?";
        return jdbcTemplate.update(sql,upopenid,id);
    }

    /**
     * 修改用户类型类型:0=粉丝,1=客户,2=一级经纪人,3=代理商,4=后续经纪人',
     * @param typedata
     * @param id
     * @return
     */
    @Override
    public int updateTypedata(@RequestParam("typedata")String typedata,
                              @RequestParam("id") String id) {
        String sql = "update  fa_user set level=? where id=?";
        return jdbcTemplate.update(sql,typedata,id);
    }

    @Override
    public int updatetoux(@RequestParam("avatar") String avatar, @RequestParam("id") String id) {
        Integer updatetime= Math.toIntExact(System.currentTimeMillis() / 1000);
        String sql = "update  fa_user set avatar=?,updatetime=? where id=?";
        return jdbcTemplate.update(sql,avatar,updatetime,id);
    }
    /**
     * 统计人数
     * @return
     */
    @Override
    public List<usercount> selectcount() {
        String sql = "SELECT COUNT(*) as count,level AS type from fa_user GROUP BY `level` ORDER BY level";

        return jdbcTemplate.query(sql,new BeanPropertyRowMapper(usercount.class));
    }

    /**
     * 修改用户余额
     * @param money
     * @param id
     * @return
     */
    @Override
    public int updateMoney(@RequestParam("money") double money,
                           @RequestParam("id") String id) {
        String sql = "update  fa_user set money=? where id=?";
        return jdbcTemplate.update(sql,money,id);
    }
}